# -*- coding: utf-8 -*-
"""
MySQL数据库连接器
"""
import pymysql
import re
from typing import Dict, List, Optional, Tuple
from utils.simple_encryption import encryption_manager

class MySQLConnector:
    """MySQL数据库连接器"""
    
    def __init__(self):
        self.connection = None
        self.encryption_manager = encryption_manager
    
    def connect(self, host: str, port: int, database: str, username: str, password: str) -> Tuple[bool, str]:
        """
        连接MySQL数据库
        
        Args:
            host: 主机地址
            port: 端口号
            database: 数据库名
            username: 用户名
            password: 密码
            
        Returns:
            (连接是否成功, 错误信息)
        """
        try:
            # 验证连接参数
            if not self._validate_connection_params(host, port, database, username, password):
                return False, "连接参数验证失败"
            
            # 建立连接
            self.connection = pymysql.connect(
                host=host,
                port=port,
                database=database,
                user=username,
                password=password,
                charset='utf8mb4',
                autocommit=True,
                connect_timeout=10
            )
            
            return True, "连接成功"
            
        except pymysql.Error as e:
            error_msg = self._get_error_message(e)
            return False, f"数据库连接失败: {error_msg}"
        except Exception as e:
            return False, f"连接异常: {str(e)}"
    
    def connect_with_config(self, config: Dict) -> Tuple[bool, str]:
        """
        使用保存的配置连接数据库
        
        Args:
            config: 数据库连接配置
            
        Returns:
            (连接是否成功, 错误信息)
        """
        try:
            # 解密密码
            password = self.encryption_manager.decrypt_password(config['encrypted_password'])
            if not password:
                return False, "密码解密失败"
            
            return self.connect(
                config['host'],
                config['port'],
                config['database_name'],
                config['username'],
                password
            )
        except Exception as e:
            return False, f"配置连接失败: {str(e)}"
    
    def test_connection(self, host: str, port: int, database: str, username: str, password: str) -> Tuple[bool, str]:
        """
        测试数据库连接
        
        Args:
            host: 主机地址
            port: 端口号
            database: 数据库名
            username: 用户名
            password: 密码
            
        Returns:
            (连接是否成功, 错误信息)
        """
        try:
            # 创建临时连接进行测试
            test_conn = pymysql.connect(
                host=host,
                port=port,
                database=database,
                user=username,
                password=password,
                charset='utf8mb4',
                connect_timeout=5
            )
            test_conn.close()
            return True, "连接测试成功"
        except pymysql.Error as e:
            error_msg = self._get_error_message(e)
            return False, f"连接测试失败: {error_msg}"
        except Exception as e:
            return False, f"连接测试异常: {str(e)}"
    
    def disconnect(self):
        """断开数据库连接"""
        if self.connection:
            try:
                self.connection.close()
            except Exception as e:
                print(f"断开连接时出错: {e}")
            finally:
                self.connection = None
    
    def is_connected(self) -> bool:
        """检查是否已连接"""
        if not self.connection:
            return False
        
        try:
            # 执行简单查询测试连接
            cursor = self.connection.cursor()
            cursor.execute("SELECT 1")
            cursor.fetchone()
            cursor.close()
            return True
        except Exception:
            return False
    
    def get_database_info(self) -> Dict:
        """获取数据库信息"""
        if not self.is_connected():
            return {}
        
        try:
            cursor = self.connection.cursor()
            
            # 获取数据库版本
            cursor.execute("SELECT VERSION()")
            version = cursor.fetchone()[0]
            
            # 获取当前数据库名
            cursor.execute("SELECT DATABASE()")
            database_name = cursor.fetchone()[0]
            
            # 获取字符集
            cursor.execute("SELECT @@character_set_database")
            charset = cursor.fetchone()[0]
            
            cursor.close()
            
            return {
                'version': version,
                'database_name': database_name,
                'charset': charset
            }
        except Exception as e:
            print(f"获取数据库信息失败: {e}")
            return {}
    
    def get_tables(self) -> List[str]:
        """获取所有表名"""
        if not self.is_connected():
            return []
        
        try:
            cursor = self.connection.cursor()
            cursor.execute("SHOW TABLES")
            tables = [row[0] for row in cursor.fetchall()]
            cursor.close()
            return tables
        except Exception as e:
            print(f"获取表列表失败: {e}")
            return []
    
    def get_table_structure(self, table_name: str) -> Dict:
        """获取表结构信息"""
        if not self.is_connected():
            return {}
        
        try:
            cursor = self.connection.cursor()
            
            # 获取表基本信息
            cursor.execute(f"""
                SELECT 
                    TABLE_COMMENT
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{table_name}'
            """)
            table_info = cursor.fetchone()
            table_comment = table_info[0] if table_info else ""
            
            # 获取字段信息
            cursor.execute(f"""
                SELECT 
                    COLUMN_NAME,
                    DATA_TYPE,
                    CHARACTER_MAXIMUM_LENGTH,
                    NUMERIC_PRECISION,
                    NUMERIC_SCALE,
                    IS_NULLABLE,
                    COLUMN_DEFAULT,
                    COLUMN_COMMENT,
                    COLUMN_KEY,
                    EXTRA
                FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{table_name}'
                ORDER BY ORDINAL_POSITION
            """)
            
            columns = []
            for row in cursor.fetchall():
                column_info = {
                    'name': row[0],
                    'type': row[1],
                    'length': row[2],
                    'precision': row[3],
                    'scale': row[4],
                    'nullable': row[5] == 'YES',
                    'default': row[6],
                    'comment': row[7] or "",
                    'key': row[8],
                    'extra': row[9]
                }
                
                # 格式化数据类型
                column_info['type_display'] = self._format_data_type(column_info)
                columns.append(column_info)
            
            # 获取索引信息
            cursor.execute(f"""
                SELECT 
                    INDEX_NAME,
                    COLUMN_NAME,
                    NON_UNIQUE,
                    INDEX_TYPE
                FROM information_schema.STATISTICS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{table_name}'
                ORDER BY INDEX_NAME, SEQ_IN_INDEX
            """)
            
            indexes = {}
            for row in cursor.fetchall():
                index_name = row[0]
                if index_name not in indexes:
                    indexes[index_name] = {
                        'name': index_name,
                        'columns': [],
                        'unique': row[2] == 0,
                        'type': row[3]
                    }
                indexes[index_name]['columns'].append(row[1])
            
            cursor.close()
            
            return {
                'name': table_name,
                'comment': table_comment,
                'columns': columns,
                'indexes': list(indexes.values())
            }
            
        except Exception as e:
            print(f"获取表结构失败: {e}")
            return {}
    
    def _validate_connection_params(self, host: str, port: int, database: str, username: str, password: str) -> bool:
        """验证连接参数"""
        # 验证主机地址
        if not host or not host.strip():
            return False
        
        # 验证端口号
        if not isinstance(port, int) or port < 1 or port > 65535:
            return False
        
        # 验证数据库名
        if not database or not database.strip():
            return False
        
        # 验证用户名
        if not username or not username.strip():
            return False
        
        # 验证密码
        if not password:
            return False
        
        return True
    
    def _format_data_type(self, column_info: Dict) -> str:
        """格式化数据类型显示"""
        data_type = column_info['type']
        length = column_info['length']
        precision = column_info['precision']
        scale = column_info['scale']
        
        if data_type in ['varchar', 'char']:
            return f"{data_type}({length})"
        elif data_type in ['decimal', 'numeric']:
            if scale and scale > 0:
                return f"{data_type}({precision},{scale})"
            else:
                return f"{data_type}({precision})"
        elif data_type in ['float', 'double']:
            if precision:
                return f"{data_type}({precision},{scale})"
            else:
                return data_type
        else:
            return data_type
    
    def _get_error_message(self, error: pymysql.Error) -> str:
        """获取友好的错误信息"""
        error_code = error.args[0]
        error_msg = error.args[1] if len(error.args) > 1 else str(error)
        
        error_messages = {
            1045: "用户名或密码错误",
            1049: "数据库不存在",
            2003: "无法连接到MySQL服务器，请检查主机地址和端口号",
            2006: "MySQL服务器连接已断开",
            2013: "连接MySQL服务器时丢失连接",
            1040: "MySQL服务器连接数过多",
            1042: "无法解析主机名",
            1043: "连接协议错误",
            1044: "访问被拒绝，请检查用户权限"
        }
        
        return error_messages.get(error_code, f"MySQL错误 {error_code}: {error_msg}")
